
class TaskSqlMap(object):
    FIND_ALL_CLASSIFY = "SELECT s.status_id,s.sort_value,s.status_name FROM aoa_status_list s where s.status_model='aoa_task_list' ORDER BY s.sort_value"

    MYTASK_LIST = "select t.task_id,t.title,t4.status_name,t.reciver_user as implement,t.star_time,t.end_time,t.progress_speed,t.priority_level " \
                  "from aoa_task_list as t " \
                  "inner join aoa_status_list as t4 on t4.status_id=t.status_id " \
                  "where (t.task_push_user_id={user_id} or t.reciverlist='{my_name}' or t.implement='{my_name}' or t.implement like '{my_name};%' or t.implement like '%;{my_name};%' or t.implement like '%;{my_name}')"

    TASK_ADD = "insert into aoa_task_list(title,status_id,task_describe,star_time,end_time,reciver_user,task_push_user_id,modify_time,publish_time,task_project_id,implement_user,reciverlist,implement,attachment_id) values('{title}',{status_id},'{task_describe}','{star_time}','{end_time}','{implement}',{task_push_user_id},now(),now(),{task_project_id},'{reciverlist}','{implement_user}','{reciverlist_user}',{attach_id})"

    TASK_USER_ADD = "insert into aoa_task_user(status_id,task_id,task_recive_user_id) values({status_id},{task_id},{task_recive_user_id})"

    FIND_NAME = "select t1.real_name,t1.user_name,t1.role_id,t2.role_value,t1.dept_id from aoa_user as t1 inner join aoa_role_ as t2 on t1.role_id=t2.role_id where t1.user_id={user_id}"
    FIND_ALL = "select user_id from aoa_user where user_id!={user_id}"
    FIND_SUB = "select t1.user_id from aoa_user as t1 inner join aoa_role_ as t2 on t1.role_id=t2.role_id where t1.dept_id=(select dept_id from aoa_user where user_id={user_id}) and t2.role_value>{my_role_id}"
    FIND_STATUS_ID = "select status_id from aoa_status_list where status_name='{status_name}' and status_model='aoa_task_list'"

    GET_ALL_OTHER = "select user_name,user_id as next_user_id from aoa_user where user_id!={user_id}"
    subordinates_task_all =  "select t.task_id,t.title,t4.status_name,t.reciver_user as implement,t.star_time,t.end_time,t.progress_speed,t.priority_level " \
                            "from aoa_task_list as t inner join aoa_status_list as t4 on t4.status_id=t.status_id " \
                            "where (t.task_push_user_id={next_user_id} or t.reciverlist='{user_name}' or t.implement='{user_name}' or t.implement like concat('%;','{user_name}',';%') or t.implement like concat('{user_name}',';%') or t.implement like concat('%;','{user_name}'))"

    GET_DOWN_ID = "select user_name,user_id as next_user_id from aoa_user inner join aoa_role_ on aoa_user.role_id = aoa_role_.role_id where dept_id=(select dept_id from aoa_user where user_id={user_id}) and aoa_role_.role_value>(select role_value from aoa_user inner join aoa_role_ on aoa_user.role_id=aoa_role_.role_id where user_id={user_id})"
    subordinates_task_sub =  "select t.task_id,t.title,t4.status_name,t.reciver_user as implement,t.star_time,t.end_time,t.progress_speed,t.priority_level " \
                            "from aoa_task_list as t inner join aoa_status_list as t4 on t4.status_id=t.status_id " \
                            "where (t.task_push_user_id={next_user_id} " \
                            "or t.reciverlist='{user_name}' or t.implement='{user_name}' or t.implement like concat('%;','{user_name}') or t.implement like concat('{user_name}',';%') or t.implement like concat('%;','{user_name}',';%'))"

    task_details = "select t6.attachment_id,t6.attachment_name,t1.title,t1.star_time,t1.end_time,t1.publish_time,t1.task_describe,t1.progress_speed,t4.project_name,t5.status_name,reciver_user as implement,implement_user as reciverlist from aoa_task_list as t1 left join project as t4 on t1.task_project_id=t4.project_id inner join aoa_status_list as t5 on t1.status_id=t5.status_id left join aoa_attachment_list as t6 on t1.attachment_id=t6.attachment_id where t1.task_id={task_id}"
    task_reciver = "select t3.real_name from aoa_task_list as t1 inner join aoa_task_user as t2 on t1.task_id=t2.task_id inner join aoa_user as t3 on t3.user_id=t2.task_recive_user_id where t1.task_id={task_id}"
    task_comment = "select t1.comment,t1.end_time as comment_time,t2.real_name,t2.img_path from aoa_task_log as t1 inner join aoa_user as t2 on t1.issue=t2.user_id where t1.task_id={task_id} order by t1.end_time desc"
    speed_setting = "update aoa_task_list set progress_speed={progress_speed},status_id={status_id} where task_id={task_id}"
    insert_comment = "insert into aoa_task_log(comment,end_time,issue,task_id) values('{comment}',now(),{user_id},{task_id})"
    SPEED_INFO = "select t1.progress_speed,t2.comment,t2.end_time,t3.real_name,t3.user_id,t3.img_path from aoa_task_list as t1 inner join aoa_task_log as t2 on t1.task_id=t2.task_id inner join aoa_user as t3 on t2.issue=t3.user_id where t1.task_id={task_id} order by t2.end_time"
    GET_ID = "select @@IDENTITY as id"


    FILTER_PARAMS = {
        "query_str": "t.title like '%{query_str}%'",
        "priority_level": "t.priority_level={priority_level}",
        "status_id": "t.status_id={status_id}"
    }